﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace MyORC
{
    class AccessHelper
    {
        protected static OleDbConnection conn = new OleDbConnection();

        protected static OleDbCommand comm = new OleDbCommand();

        public static string connStr = "";

        public static string binPath = Directory.GetCurrentDirectory();

        /***
         * 
         * 打开数据库链接
         * ***/
        private static void openConnection()
		{
			if (conn.State == ConnectionState.Closed)
			{
				
				if (connStr == "")
				{
					conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + binPath + "\\" + ConfigurationManager.AppSettings["myconn"].ToString();
                    Console.WriteLine(conn.ConnectionString);
				}
				else
				{
					conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + connStr;
                    Console.WriteLine(conn.ConnectionString);
                }
				comm.Connection = conn;
				try
				{
					conn.Open();
				}
				catch (Exception e)
				{
					throw new Exception(e.Message);
				}
			}
		}

       
        /***
        * 
        * 关闭数据库链接
        * ***/
        public static void closeConnection()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();
                comm.Dispose();
            }
        }

     

        /***
       * 
       * 执行sql语句，无返回值
       * ExecuteNonQuery方法主要用来更新数据，当然也可以用来执行目标操作（例如查询数据库的结构或者创建诸如表等的数据库对象）。
       * 通常用它来执行insert、update、delete语句，在不使用Dataset的情况下更改数据库中的数据。
       * select语句不适合ExecuteNonQuery()方法。
       * ***/
        public static void excuteSql(string sql)
        {
            if (conn.State == ConnectionState.Closed)
            {
                try
                {
                    openConnection();
                    comm.CommandType = CommandType.Text;
                    comm.CommandText = sql;
                    comm.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    closeConnection();
                }
            }
        }

        public static void excuteSql(string dbPath, string sql)
        {
            if (conn.State == ConnectionState.Closed)
            {
                connStr = dbPath;
                try
                {
                    openConnection();
                    comm.CommandText = sql;
                    comm.CommandType = CommandType.Text;
                    comm.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    closeConnection();
                }
            }
        }


        public static void excuteSqlNoClose(string dbPath, string sql)
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    connStr = dbPath;
                    openConnection();
                }
                comm.CommandText = sql;
                comm.CommandType = CommandType.Text;
                comm.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

        }

        /***
         * 
         * 获取数据库中所有的表的名字
         * 参数为数据库名
         * 返回值为DataTable
         * dt.Rows[i].ItemArray[2].ToString(),获取表名
         * TABLE_CATALOG、TABLE_SCHEMA、TABLE_NAME、TABLE_TYPE
         * ***/

        public static List<string> GetTableName(string dbPath)
        {
            DataTable dt = new DataTable();
            List<string> tableNames = new List<string>();
            if (conn.State == ConnectionState.Closed)
            {
                connStr = dbPath;
                try
                {
                    openConnection();
                    dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[4]
                    {
                        null,
                        null,
                        null,
                        "TABLE"
                    });
                    Console.WriteLine("aa");
                    for(int i = 0; i < dt.Rows.Count; i++)
                    {
                        tableNames.Add(dt.Rows[i].ItemArray[2].ToString());
                    }
                    
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    closeConnection();
                }
            }
            
            return tableNames;
        }

        public static DataTable getDataTabale(string dbPath, string sql)
        {
            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter();
            if (conn.State == ConnectionState.Closed)
            {
                connStr = dbPath;
               
                try
                {
                    openConnection();
                    comm.CommandType = CommandType.Text;
                    comm.CommandText = sql;
                    da.SelectCommand = comm;
                    da.Fill(dt);
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    closeConnection();
                }
            }
            return dt;
        }

        public static DataTable getDataTabaleNoClose(string dbPath, string sql)
        {
            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter();
            connStr = dbPath;
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    openConnection();
                }
                comm.CommandType = CommandType.Text;
                comm.CommandText = sql;
                da.SelectCommand = comm;
                da.Fill(dt);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return dt;
        }
    }
}
